Purpose

this report presents the business case for the formulation of a Customer Segmentation strategy by utilizing the information available in a CRM system.

CRM Extract

The data we are analysing refer to a multinational IT company which sells 62 unique products across 4 Product Categories in 17 countries. Key Account managers list opportunities in the company’s CRM system each opportunity takes an unique opportunity ID.

lets have a look in our crm data

#importing the data in a dataframe from csv file
crm<-read.csv("crm.csv", header = TRUE,sep = ";")

describe(crm)

## crm 
## 
##  18  Variables      5766  Observations
## --------------------------------------------------------------------------------
## Billing.Country 
##        n  missing distinct 
##     5766        0       17 
## 
## lowest : Albania                Bosnia and Herzegovina Bulgaria               Croatia                Cyprus                
## highest: Serbia                 Slovakia               Slovenia               Turkey                 Ukraine               
## 
## Albania (235, 0.041), Bosnia and Herzegovina (257, 0.045), Bulgaria (1604,
## 0.278), Croatia (578, 0.100), Cyprus (65, 0.011), Czech Republic (109, 0.019),
## FYROM (158, 0.027), Greece (541, 0.094), Kosovo (149, 0.026), Moldova, Republic
## of (9, 0.002), Montenegro (48, 0.008), Romania (517, 0.090), Serbia (222,
## 0.039), Slovakia (215, 0.037), Slovenia (794, 0.138), Turkey (43, 0.007),
## Ukraine (222, 0.039)
## --------------------------------------------------------------------------------
## Opportunity.ID 
##        n  missing distinct 
##     5766        0     2042 
## 
## lowest : OPP-000999 OPP-001000 OPP-001078 OPP-001192 OPP-001193
## highest: OPP-011751 OPP-011753 OPP-011757 OPP-011804 OPP-012009
## --------------------------------------------------------------------------------
## Account.Name 
##        n  missing distinct     Info     Mean      Gmd      .05      .10 
##     5766        0     2042        1     8623     1929     2850     7383 
##      .25      .50      .75      .90      .95 
##     8183     8780     9819    10632    10916 
## 
## lowest :   999  1000  1078  1192  1193, highest: 11751 11753 11757 11804 12009
## --------------------------------------------------------------------------------
## Parent.Account 
##        n  missing distinct 
##     5766        0       15 
## 
## lowest :           Parent_1  Parent_10 Parent_11 Parent_12
## highest: Parent_5  Parent_6  Parent_7  Parent_8  Parent_9 
## 
## (4157, 0.721), Parent_1 (49, 0.008), Parent_10 (61, 0.011), Parent_11 (6,
## 0.001), Parent_12 (62, 0.011), Parent_13 (8, 0.001), Parent_14 (2, 0.000),
## Parent_2 (457, 0.079), Parent_3 (172, 0.030), Parent_4 (75, 0.013), Parent_5
## (293, 0.051), Parent_6 (175, 0.030), Parent_7 (65, 0.011), Parent_8 (130,
## 0.023), Parent_9 (54, 0.009)
## --------------------------------------------------------------------------------
## Type 
##        n  missing distinct 
##     5766        0        2 
##                                               
## Value      Existing Business      New Business
## Frequency               4867               899
## Proportion             0.844             0.156
## --------------------------------------------------------------------------------
## Invoice.Date 
##        n  missing distinct 
##     5766        0      283 
## 
## lowest : 1/13/2017 1/14/2017 1/16/2017 1/17/2017 1/19/2017
## highest: 9/26/2017 9/27/2017 9/29/2017 9/30/2016 9/30/2017
## --------------------------------------------------------------------------------
## Schedule.Date 
##        n  missing distinct 
##     5766        0      321 
## 
## lowest : 1/1/17    1/10/17   1/11/17   1/12/17   1/13/2017
## highest: 9/3/17    9/30/2017 9/5/17    9/6/17    9/8/17   
## --------------------------------------------------------------------------------
## Amount..converted..Currency 
##        n  missing distinct    value 
##     5766        0        1      EUR 
##                
## Value       EUR
## Frequency  5766
## Proportion    1
## --------------------------------------------------------------------------------
## Amount..converted. 
##        n  missing distinct     Info     Mean      Gmd      .05      .10 
##     5766        0     1774        1   113208   178297    370.3    763.1 
##      .25      .50      .75      .90      .95 
##   3717.8  19584.0  82612.9 335143.4 653012.1 
## 
## lowest :       0.27      15.34      15.73      26.60      29.00
## highest: 1830000.00 2052300.00 2075910.32 2090000.00 2104000.00
## --------------------------------------------------------------------------------
## Quantity 
##        n  missing distinct     Info     Mean      Gmd      .05      .10 
##     5766        0      349    0.974     2474     4723        1        1 
##      .25      .50      .75      .90      .95 
##        1       12      242     1858     7047 
## 
## lowest :      1.0      2.0      3.0      3.5      4.0
## highest:  52800.0  62500.0  74259.0 149829.0 552715.0
##                                                                          
## Value           0   5000  10000  15000  20000  30000  35000  40000  45000
## Frequency    5247    237    119     40     53     13      1      5     24
## Proportion  0.910  0.041  0.021  0.007  0.009  0.002  0.000  0.001  0.004
##                                              
## Value       55000  60000  75000 150000 555000
## Frequency       1      1     12      1     12
## Proportion  0.000  0.000  0.002  0.000  0.002
## 
## For the frequency table, variable is rounded to the nearest 5000
## --------------------------------------------------------------------------------
## Schedule.Amount..converted..Currency 
##        n  missing distinct    value 
##     5766        0        1      EUR 
##                
## Value       EUR
## Frequency  5766
## Proportion    1
## --------------------------------------------------------------------------------
## Schedule.Amount..converted. 
##         n   missing  distinct      Info      Mean       Gmd       .05       .10 
##      5766         0      3109         1     30864     54336     51.13    145.02 
##       .25       .50       .75       .90       .95 
##    600.00   2375.00  11063.05  46558.75 125000.00 
## 
## lowest :  -11394.63   -3436.90   -1986.04   -1315.20    -986.40
## highest: 1038826.44 1122000.00 1162431.00 1304000.00 1572100.00
## --------------------------------------------------------------------------------
## Gross.Margin.. 
##        n  missing distinct     Info     Mean      Gmd      .05      .10 
##     5766        0      542    0.996    45.05     33.5    3.907    6.895 
##      .25      .50      .75      .90      .95 
##   20.000   43.730   60.000  100.000  100.000 
## 
## lowest :   0.01   0.10   0.97   1.00   1.38, highest: 119.00 179.00 308.00 630.00 900.00
##                                                                             
## Value          0    10    20    30    40    50    60    70    80    90   100
## Frequency    475   569   782   493   646   920   704   203   249    55   665
## Proportion 0.082 0.099 0.136 0.086 0.112 0.160 0.122 0.035 0.043 0.010 0.115
##                                         
## Value        120   180   310   630   900
## Frequency      1     1     1     1     1
## Proportion 0.000 0.000 0.000 0.000 0.000
## 
## For the frequency table, variable is rounded to the nearest 10
## --------------------------------------------------------------------------------
## Stage 
##        n  missing distinct 
##     5766        0        2 
##                                 
## Value      Closed Won   Invoiced
## Frequency        1878       3888
## Proportion      0.326      0.674
## --------------------------------------------------------------------------------
## Probability.... 
##        n  missing distinct     Info     Mean      Gmd 
##     5766        0        2    0.659    96.74    4.393 
##                       
## Value         90   100
## Frequency   1878  3888
## Proportion 0.326 0.674
## --------------------------------------------------------------------------------
## Forecast.Category 
##        n  missing distinct 
##     5766        0        2 
##                         
## Value      Closed Commit
## Frequency    3888   1878
## Proportion  0.674  0.326
## --------------------------------------------------------------------------------
## Product.Name 
##        n  missing distinct 
##     5766        0       62 
## 
## lowest : Product_1  Product_10 Product_11 Product_12 Product_13
## highest: Product_61 Product_62 Product_7  Product_8  Product_9 
## --------------------------------------------------------------------------------
## Product_.Category 
##        n  missing distinct 
##     5766        0        4 
##                                                           
## Value         Hardware Outsourcing    Services    Software
## Frequency         1662         241        2961         902
## Proportion       0.288       0.042       0.514       0.156
## --------------------------------------------------------------------------------

as we can see our data constist of 18 variables and 5766 observations and there are no missing observations for the our variables.

Above we can find the relative frequency and the proportion were applicable for each variable and drow the following conclusions:

• 3888 opportunities are “Invoiced” and 1878 are “Closed-Won”" this means that we can use the whole dataset in our analysis.
• 3 Countries are generating 52% of the total sales (Bulgaria: 28%, Slovenia: 14%, Croatia : 10%).
• 85% of the total Sales are coming from Existing Customers vs 15% that comes from New.
• 51% of Sales is coming from Services, 29% from Hardware, 16% from Software and 4% from Outsourcing
• There are 2042 distinct Accounts (i.e. Customers). Also there are 15 Parent Accounts (country )
• Mean Revenue from a sale is 30.864 €
• Mean Gross Marging Percentage of a Sale is 45.05%

#taking a random sample of our data 0,5%
(formattable(sample_frac(crm,0.005)))

What is RFM analysis

RFM Analysis

We will use for our analyis on the Schedule.Amount..converted. and the Schedule.Date fields for the following reason since there are opportunities which are depicted in the system with multiple lines for the same Opportunity.ID those two fields correspond to the true sale revenue and the true invoice date. Also we will group our crm dataset by Billing.Country,Account.Name and Parent.Account cause we must define the whole purchasing history we have with each customer and each customer might have various opportunities listed to them.

we choose as a report date 31/12/2017 Now we need to calculate some new variables

• Revenue: Invoiced Revenue corresponding to each customer.In order to calculate revenue we will sum up all the Schedule.Amount..converted. per Account.Name.

• GrossProffit:Gross Proffit per customer.In order to calculate Gross Profit we will used the following formula Revenue x Gross.Margin...

• Recency: When did we last invoiced the customer, counted in days. In order to calculate recency we will take the difference in days from our report date (i.e.31/12/2017) from the maximum Schedule.Date for each customer. This ensures that if a customer has more than one opportunities listed the most recent invoice date will be used for the calculation.

• Frequency: How often do we invoice the customer. In order to calculate frequency will count the distict Schedule.Date for each customer.So if we have issued multiple invoices related with multiple opportunities for the same customer in the same date this will be counted as a one time invoicing.

#Setting a report date 
rdate2017=as.Date("2017-12-31")

# CRM data wrangling 1 :date transformations and filtering and saving the results in a new dataframe named seg2017
seg2017<-crm%>%mutate(`Schedule.Date`=mdy(`Schedule.Date`))%>%filter(Schedule.Date > "2016-01-31" & Schedule.Date <"2018-01-01")
#%>%mutate(`Invoice.Date`=mdy(`Invoice.Date`))

#CRM data wrangling 2:calculating Gross Profit in the seg2017 dataframe
seg2017<-seg2017%>%mutate(`Gross.Profit`=(Schedule.Amount..converted.*Gross.Margin..)/100)

# grouping seg2017 calculating Recency/Frequncy/Monetary/Profit and Gross Profit 2017 and saving the results in a new datarame named RFM_seg2017
RFM_seg2017<-seg2017%>%group_by(Billing.Country,Account.Name,Parent.Account)%>%
  summarise(Recency=as.numeric(rdate2017-max(Schedule.Date)),Frequency=n_distinct(Schedule.Date),Monetary= sum(as.numeric(Schedule.Amount..converted.),na.rm =TRUE)/n_distinct(Schedule.Date),Revenue=sum(as.numeric(Schedule.Amount..converted.),na.rm=TRUE),  `Gross.Profit`=sum(Gross.Profit,na.rm=TRUE))

# Order the RFM_seg2017 dataframe by Revenue
RFM_seg2017<-RFM_seg2017[order(-RFM_seg2017$Revenue),]

#present a few lines of RFM_seg2017
formattable(head(RFM_seg2017,15))

#Calucalating R/F/M Quantiles 2017: taking the 3 columns (Recency, Frequency,Monetary) and scalling them 
rfm_quant_2017 <- RFM_seg2017[c(4:6)]%>%mutate(R=ntile(desc(Recency),5),F=ntile(Frequency,5),M=ntile(Monetary,5))%>%mutate(RFM= paste0(R,F,M))%>%mutate(RFM=as.numeric(RFM))%>%mutate(FM=ntile(F+M,5))

#creating an id column in order to join the 2 dataframes
 RFM_seg2017$id= 1:nrow(RFM_seg2017)
rfm_quant_2017$id=1:nrow(rfm_quant_2017)
RFM_seg2017<-full_join(RFM_seg2017,rfm_quant_2017)

## Joining, by = c("Recency", "Frequency", "Monetary", "id")

#dropping the id columnn 
RFM_seg2017 <- RFM_seg2017[-c(9)]

#characterise customers based on their RFM score 
RFM_seg2017<-RFM_seg2017%>%mutate(Segment=if_else(R%in% c(5)&FM%in%c(5),"Champions",if_else(R%in% c(4:5)&FM%in%c(4:5),"Loyal",if_else(R%in% c(4:5)&FM%in%c(0:3),"Promising",if_else(R%in% c(2:3)&FM%in%c(2:3),"Need Attention",if_else(R%in% c(2:4)&FM%in%c(1:4),"Slipping",if_else(R%in% c(1:3)&FM%in%c(0:5),"At Risk",false=NULL)))))))

formattable(head(RFM_seg2017,15))

Visualizations per Segment

#Ordering vector
myorder<-c('Champions','Loyal','Promising','Need Attention','Slipping','At Risk')

#Grouping RFM_seg2017 by Segment and creating a new df named revseg2017
revseg2017<-RFM_seg2017%>%group_by(Segment)%>%summarise(Revenue=sum(as.numeric(Revenue),na.rm=TRUE),No.Customers=sum(n_distinct(Account.Name)))%>%arrange(factor(Segment, levels =myorder))
revseg2017$Revenue<-currency(revseg2017$Revenue,symbol = "€",digits=0)

#showing revseg2017
formattable(revseg2017)

library(treemapify)
library(ggfittext)
library(viridis)
vectorc=c("red","orange","blue","green")

tree<-ggplot(revseg2017, aes(area =Revenue, fill =Revenue, label = paste(No.Customers,Segment,format(Revenue/1000000 ,digits=0,nsmall=2, big.mark=","),"M€")))+geom_treemap() +geom_treemap_text(fontface = "italic", colour = "white", place = "centre",grow = T,reflow=T,size=60)+theme(legend.position = "right")+labs(title =paste("Revenue by Customer Segment\n Total Revenue=",format(sum(revseg2017$Revenue),big.mark = ","),"€"),caption = "The area of each tile represents the proportion of Revenue")+scale_fill_continuous(limits=c(000000, 90000000), breaks=seq(0000000, 90000000,by=15000000),low="orangered1",high="darkblue",label = dollar_format(suffix = "€", prefix = ""))

tree

library(plotly)

pie <-revseg2017%>% plot_ly( labels = ~Segment, values = ~Revenue )%>%add_pie(hole = 0.6) %>%layout(title = 'Revenue by Segment',xaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE),
         yaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE)) 
#type = 'pie'after values in order to get a pie chart)>

pie%>%layout(showlegend = TRUE)%>% config(displayModeBar = F)

p <-revseg2017%>% plot_ly( y = ~Segment, x = ~Revenue, type = 'bar',orientation = 'h',height=450,width=750)%>%layout(margin =list(l = 110, r = 100, b = 50, t =50, pad = 4))
p%>%layout%>% config(displayModeBar = F)

Visualizations by Product

newdf<-merge(seg2017, RFM_seg2017[, c("Account.Name", "Segment")], by="Account.Name")

revprod<-newdf%>%group_by(Product.Name,Segment)%>% summarise(Revenue=sum(Schedule.Amount..converted.,na.rm=TRUE),No.Customers=sum(n_distinct(Account.Name)))

a<-revprod%>%group_by(Product.Name)%>%summarise(Revenue=sum(Revenue,na.rm=TRUE),No.Customers=sum(No.Customers,na.rm=TRUE))
a$Total="Total"
#unite(a, Product.Name, Total, sep=" ")


c<-bind_rows(revprod,a)
c[order(c$Product.Name),]

## # A tibble: 317 x 5
## # Groups:   Product.Name [62]
##    Product.Name Segment    Revenue No.Customers Total
##    <fct>        <chr>        <dbl>        <int> <chr>
##  1 Product_1    At Risk    138293.            4 <NA> 
##  2 Product_1    Champions 1452807.           10 <NA> 
##  3 Product_1    Loyal       37801.            2 <NA> 
##  4 Product_1    <NA>      1628901.           16 Total
##  5 Product_10   Loyal       27435.            6 <NA> 
##  6 Product_10   Promising   36649.            4 <NA> 
##  7 Product_10   Slipping    36571.            7 <NA> 
##  8 Product_10   <NA>       100655.           17 Total
##  9 Product_11   At Risk    168219.            5 <NA> 
## 10 Product_11   Champions   48758.            1 <NA> 
## # … with 307 more rows

d<-c%>%unite(Product.Name, Product.Name, Total, sep=" ")
d$Product.Name<- gsub("NA"," ",d$Product.Name)
d$Segment<- gsub("NAN","a",d$Segment)
d<-d[order(c$Product.Name,c$Revenue),]
d<-d%>%mutate(Segment=replace(Segment,is.na(Segment),""))%>%mutate(No.Customers=replace(No.Customers,is.na(No.Customers),""))
d$Revenue<-currency(d$Revenue,symbol = "€",digits=0)
formattable(d)

segtoview="At Risk"
la<-revprod%>%filter(Segment==segtoview)

tree2<-revprod%>%filter(Segment==segtoview)%>%ggplot(aes(area =Revenue, fill =Revenue, label = paste(Product.Name,format(as.numeric(Revenue/1000000),digits=0,nsmall=2, big.mark=","),"M€")))+geom_treemap() +geom_treemap_text(fontface = "italic", colour = "white", place = "centre",grow = T,reflow=T)+theme(legend.position = "right")+labs(title =paste("Revenue per Product" ,segtoview, "Segment\n",segtoview, "Revenue=",format(sum(la$Revenue),big.mark = ","),"€"),caption = "The area of each tile represents the proportion of Revenue")+scale_fill_continuous(low="orangered1",high="darkblue",label = dollar_format(suffix = "€", prefix = ""))

tree2

revseg2017%>%ggplot(aes(Segment,Revenue))+geom_bar(stat="identity",fill="steel blue")+geom_text(aes(label=Revenue),size=3.5)+scale_y_continuous(breaks=seq(0,90000000,15000000),labels = dollar_format(suffix = "€", prefix = ""))+theme_minimal()+coord_flip()

#+scale_x_discrete(limits=rev(myorder))+coord_flip()